
const db = require('db');
const log = require('tools/log');
const { getTokenInof } = require('tools/token');
const isLeader = require('tools/isLeader');

module.exports = async ctx => {
    const { connection, run } = db.init();
    try {
        ctx.status = 200;
        ctx.body = {
            list: await list(ctx, run),
            pagination: await pagination(ctx, run)
        };
    } catch (error) {
        ctx.status = 500;
        ctx.body = error;
        log('---------------------------------------------------');
        console.log(error);
    }
    connection.end();
}

/** 获取列表 */
async function list(ctx, run) {
    const body = ctx.request.body;
    const pagination = body.pagination;
    const sqlStr = `
        SELECT 
            orders.id,
            orders.company,
            orders.commercialSpecialistId,
            orders.commercialSpecialistName,
            orders.commercialSpecialistEmail,
            orders.pmId,
            orders.pmName,
            orders.pmEmail,
            orders.orderStatus,
            orders.orderTime,
            orders.orderNo,
            orders.deptName,
            orders.deptId,
            orders.clientId,
            orders.project,
            orders.cooperationMode,
            orders.undertakingType,
            orders.details,
            orders.planStartTime,
            orders.startTime,
            orders.scheduledCompletionTime,
            orders.produceCompletionTime,
            orders.completionTime,
            orders.price,
            orders.personDays,
            orders.businessDay,
            orders.amount,
            orders.paymentCycle,
            orders.billingCycle,
            c.clientName
        FROM orders, client as c
        WHERE 0=0 AND c.id = orders.clientId
        ${permission(ctx)}
        ${toWhereSql(body)}
        ${toSorterSql(body.sorter, `ORDER BY orderTime DESC`)}
        ${toLimit(pagination)}
    `
    const result = await run(sqlStr);
    
    if (result.err) {
        throw result.err
    }

    return result.rows;
}

/** 获取分页数据 */
async function pagination(ctx, run) {
    const body = ctx.request.body;
    const pagination = body.pagination;
    const sqlStr = `
        SELECT COUNT(*) as total
        FROM orders, client as c 
        WHERE 0=0 AND c.id = orders.clientId
        ${permission(ctx)}
        ${toWhereSql(body)}
    `
    const result = await run(sqlStr);
    if (result.err) {
        throw result.err
    }

    return {
        ...pagination,
        total: result.rows[0].total
    };
}

// 数据库sql脚本生成 ----------------------------------------------------

/** 分页语句 */
function toLimit(pagination) {
    const { current = 1, pageSize = 10 } = pagination || {};
    return `LIMIT ${(current - 1) * pageSize},${pageSize}`;
}

/** 排序 */
function toSorterSql(sorter, def) {
    if (!(sorter && sorter.order)) return def;
    if (sorter.order === 'ascend') {
        return `ORDER BY ${sorter.columnKey} ASC`;
    } if (sorter.order === 'descend') {
        return `ORDER BY ${sorter.columnKey} DESC`;
    } else {
        return def
    }
}

/** 筛选 */
function toWhereSql(body) {
    console.log(body);

    const it = []

    body.orderStatus && it.push(`orders.orderStatus = '${body.orderStatus}'`);
    body.orderNo && it.push(`orders.orderNo LIKE '%${body.orderNo}%'`);
    body.company && it.push(`orders.company = '${body.company}'`);
    body.commercialSpecialist && it.push(`orders.commercialSpecialistId = '${body.commercialSpecialist.userid}'`);
    body.pm && it.push(`orders.pmId = '${body.pm.userid}'`);
    body.orderTime && it.push(`orders.orderTime BETWEEN '${body.orderTime[0]} 00:00:00' AND '${body.orderTime[1]} 23:59:59'`);
    body.clientId && it.push(`orders.clientId = ${body.clientId}`);
    body.project && it.push(`orders.project LIKE '%${body.project}%'`);
    body.cooperationMode && it.push(`orders.cooperationMode LIKE '%${body.cooperationMode}%'`);
    body.undertakingType && it.push(`orders.undertakingType LIKE '%${body.undertakingType}%'`);
    body.planStartTime && it.push(`orders.planStartTime BETWEEN '${body.planStartTime[0]} 00:00:00' AND '${body.planStartTime[1]} 23:59:59'`);
    body.startTime && it.push(`orders.startTime BETWEEN '${body.startTime[0]} 00:00:00' AND '${body.startTime[1]} 23:59:59'`);
    body.scheduledCompletionTime && it.push(`orders.scheduledCompletionTime BETWEEN '${body.scheduledCompletionTime[0]} 00:00:00' AND '${body.scheduledCompletionTime[1]} 23:59:59'`);
    body.produceCompletionTime && it.push(`orders.produceCompletionTime BETWEEN '${body.produceCompletionTime[0]} 00:00:00' AND '${body.produceCompletionTime[1]} 23:59:59'`);
    body.completionTime && it.push(`orders.completionTime BETWEEN '${body.completionTime[0]} 00:00:00' AND '${body.completionTime[1]} 23:59:59'`);
    body.price?.[0] && it.push(`orders.price >= ${body.price[0]}`);
    body.price?.[1] && it.push(`orders.price <= ${body.price[1]}`);
    body.personDays?.[0] && it.push(`orders.personDays >= ${body.personDays[0]}`);
    body.personDays?.[1] && it.push(`orders.personDays <= ${body.personDays[1]}`);
    body.businessDay?.[0] && it.push(`orders.businessDay >= ${body.businessDay[0]}`);
    body.businessDay?.[1] && it.push(`orders.businessDay <= ${body.businessDay[1]}`);
    body.amount?.[0] && it.push(`orders.amount >= ${body.amount[0]}`);
    body.amount?.[1] && it.push(`orders.amount <= ${body.amount[1]}`);
    body.paymentCycle?.[0] && it.push(`orders.paymentCycle >= ${body.paymentCycle[0]}`);
    body.paymentCycle?.[1] && it.push(`orders.paymentCycle <= ${body.paymentCycle[1]}`);
    body.billingCycle?.[0] && it.push(`orders.billingCycle >= ${body.billingCycle[0]}`);
    body.billingCycle?.[1] && it.push(`orders.billingCycle <= ${body.billingCycle[1]}`);

    console.log(it.map(e=>` AND ${e}`).join(''))
    return it.map(e=>` AND ${e}`).join('');
}

/** 数据权限 */
function permission(ctx) {
    const userInfo = getTokenInof(ctx.header.authorization).userInfo;
    const isL = isLeader(userInfo);

    if (isL) return '';

    return `AND (orders.commercialSpecialistId=${userInfo.userid} OR orders.pmId=${userInfo.userid})`
}